<?php
/*
==============================================================================
	Dokeos - elearning and course management software

	Copyright (c) 2004-2005 Dokeos S.A.
	Copyright (c) Roan Embrechts, Vrije Universiteit Brussel
	Copyright (c) Patrick Cool, Ghent University
	Copyright (c) Yannick Warnier, Dokeos S.A.
	Copyright (c) Bart Mollet, Hogeschool Gent

	For a full list of contributors, see "credits.txt".
	The full license can be read in "license.txt".

	This program is free software; you can redistribute it and/or
	modify it under the terms of the GNU General Public License
	as published by the Free Software Foundation; either version 2
	of the License, or (at your option) any later version.

	See the GNU General Public License for more details.

	Contact address: Dokeos, 44 rue des palais, B-1030 Brussels, Belgium
	Mail: info@dokeos.com
==============================================================================
*/
/**
==============================================================================
*	This is the main database library for Dokeos.
*	Include/require it in your code to use its functionality.
*   Because this library contains all the basic database calls, it could be
*   replaced by another library for say, PostgreSQL, to actually use Dokeos
*   with another database (this is not ready yet because a lot of code still
*   uses the MySQL database functions extensively).
*
*	@package dokeos.library
* 	@todo the table constants have all to start with TABLE_
* 		  This is because of the analogy with the tool constants TOOL_
==============================================================================
*/
/*
==============================================================================
		CONSTANTS
==============================================================================
*/
//main database tables
define('TABLE_MAIN_COURSE', 'course');
define('TABLE_MAIN_USER', 'user');
define('TABLE_MAIN_CLASS', 'class');
define('TABLE_MAIN_ADMIN', 'admin');
define('TABLE_MAIN_COURSE_CLASS', 'course_rel_class');
define('TABLE_MAIN_COURSE_USER', 'course_rel_user');
define('TABLE_MAIN_CLASS_USER', 'class_user');
define('TABLE_MAIN_CATEGORY', 'course_category');
define('TABLE_MAIN_COURSE_MODULE', 'course_module');
define('TABLE_MAIN_SYSTEM_ANNOUNCEMENTS', 'sys_announcement');
define('TABLE_MAIN_LANGUAGE', 'language');
define('TABLE_MAIN_SETTINGS_OPTIONS', 'settings_options');
define('TABLE_MAIN_SETTINGS_CURRENT', 'settings_current');
define('TABLE_MAIN_SESSION', 'session');
define('TABLE_MAIN_SESSION_COURSE', 'session_rel_course');
define('TABLE_MAIN_SESSION_USER', 'session_rel_user');
define('TABLE_MAIN_SESSION_CLASS', 'session_rel_class');
define('TABLE_MAIN_SESSION_COURSE_USER', 'session_rel_course_rel_user');
define('TABLE_MAIN_SHARED_SURVEY', 'shared_survey');
define('TABLE_MAIN_SHARED_SURVEY_QUESTION', 'shared_survey_question');
define('TABLE_MAIN_SHARED_SURVEY_QUESTION_OPTION', 'shared_survey_question_option');

//statistic database tables
define('TABLE_STATISTIC_TRACK_E_LASTACCESS', 'track_e_lastaccess');
define('TABLE_STATISTIC_TRACK_E_ACCESS', 'track_e_access');
define('TABLE_STATISTIC_TRACK_E_LOGIN', 'track_e_login');
define('TABLE_STATISTIC_TRACK_E_DOWNLOADS', 'track_e_downloads');
define('TABLE_STATISTIC_TRACK_E_LINKS', 'track_e_links');
define('TABLE_STATISTIC_TRACK_E_ONLINE', 'track_e_online');
define('TABLE_STATISTIC_TRACK_E_HOTPOTATOES', 'track_e_hotpotatoes');
define('TABLE_STATISTIC_TRACK_E_COURSE_ACCESS', 'track_e_course_access');
define('TABLE_STATISTIC_TRACK_E_EXERCICES', 'track_e_exercices');
define('TABLE_STATISTIC_TRACK_E_ATTEMPT', 'track_e_attempt');
define('TABLE_STATISTIC_TRACK_E_DEFAULT', 'track_e_default');
define('TABLE_STATISTIC_TRACK_E_UPLOADS','track_e_uploads');

//scorm database tables
define('TABLE_SCORM_MAIN', 'scorm_main');
define('TABLE_SCORM_SCO_DATA', 'scorm_sco_data');

//course tables
define('TABLE_AGENDA', 'calendar_event');
define('TABLE_ANNOUNCEMENT', 'announcement');
define('TABLE_CHAT_CONNECTED', 'chat_connected'); // @todo: probably no longer in use !!!
define('TABLE_COURSE_DESCRIPTION', 'course_description');
define('TABLE_DOCUMENT', 'document');
define('TABLE_ITEM_PROPERTY', 'item_property');
define('TABLE_LINK', 'link');
define('TABLE_LINK_CATEGORY', 'link_category');
define('TABLE_TOOL_LIST', 'tool');
define('TABLE_TOOL_INTRO', 'tool_intro');
define('TABLE_SCORMDOC', 'scormdocument');
define('TABLE_STUDENT_PUBLICATION', 'student_publication');
define('CHAT_CONNECTED_TABLE', 'chat_connected');

//course forum tables
define('TABLE_FORUM_CATEGORY','forum_category');
define('TABLE_FORUM','forum_forum');
define('TABLE_FORUM_THREAD','forum_thread');
define('TABLE_FORUM_POST','forum_post');
//course group tables
define('TABLE_GROUP', 'group_info');
define('TABLE_GROUP_USER', 'group_rel_user');
define('TABLE_GROUP_TUTOR', 'group_rel_tutor');
define('TABLE_GROUP_CATEGORY', 'group_category');
//course dropbox tables
define('TABLE_DROPBOX_CATEGORY','dropbox_category');
define('TABLE_DROPBOX_FEEDBACK','dropbox_feedback');
define('TABLE_DROPBOX_POST','dropbox_post');
define('TABLE_DROPBOX_FILE','dropbox_file');
define('TABLE_DROPBOX_PERSON','dropbox_person');
//course quiz tables
define('TABLE_QUIZ_QUESTION', 'quiz_question');
define('TABLE_QUIZ_TEST', 'quiz');
define('TABLE_QUIZ_ANSWER', 'quiz_answer');
define('TABLE_QUIZ_TEST_QUESTION', 'quiz_rel_question');
//linked resource table
define('TABLE_LINKED_RESOURCES', 'resource');
//learnpath tables
define('TABLE_LEARNPATH_MAIN', 'learnpath_main');
define('TABLE_LEARNPATH_CHAPTER', 'learnpath_chapter');
define('TABLE_LEARNPATH_ITEM', 'learnpath_item');
define('TABLE_LEARNPATH_USER', 'learnpath_user');
//new scorm tables
define('TABLE_LP_MAIN', 'lp');
define('TABLE_LP_ITEM', 'lp_item');
define('TABLE_LP_VIEW', 'lp_view');
define('TABLE_LP_ITEM_VIEW', 'lp_item_view');
define('TABLE_LP_IV_INTERACTION', 'lp_iv_interaction'); // IV = Item View
// Smartblogs (Kevin Van Den Haute::kevin@develop-it.be)
// permission tables
define('TABLE_PERMISSION_USER', 'permission_user');
define('TABLE_PERMISSION_TASK', 'permission_task');
define('TABLE_PERMISSION_GROUP', 'permission_group');
// roles tables
define('TABLE_ROLE', 'role');
define('TABLE_ROLE_PERMISSION', 'role_permissions');
define('TABLE_ROLE_USER', 'role_user');
define('TABLE_ROLE_GROUP', 'role_group');
// blogs tables
define('TABLE_BLOGS', 'blog');
define('TABLE_BLOGS_POSTS', 'blog_post');
define('TABLE_BLOGS_COMMENTS', 'blog_comment');
define('TABLE_BLOGS_REL_USER', 'blog_rel_user');
define('TABLE_BLOGS_TASKS', 'blog_task');
define('TABLE_BLOGS_TASKS_REL_USER', 'blog_task_rel_user');
define('TABLE_BLOGS_RATING', 'blog_rating');
define('TABLE_BLOGS_TASKS_PERMISSIONS', 'permission_task');
//end of Smartblogs
// user information tables
define('TABLE_USER_INFO', 'userinfo_def');
define('TABLE_USER_INFO_CONTENT', 'userinfo_content');
// course settings table
define('TABLE_COURSE_SETTING', 'course_setting');
// course online tables
define('TABLE_ONLINE_LINK', 'online_link');
define('TABLE_ONLINE_CONNECTED', 'online_connected');
// dokeos_user database
define('TABLE_PERSONAL_AGENDA', 'personal_agenda');
define('TABLE_USER_COURSE_CATEGORY', 'user_course_category');
//Survey
// @todo: are these MAIN tables or course tables ?
define('TABLE_MAIN_SURVEY', 'survey');
define('TABLE_MAIN_GROUP', 'survey_group');
define('TABLE_MAIN_SURVEYQUESTION', 'questions');
// SURVEY
define('TABLE_SURVEY', 'survey');
define('TABLE_SURVEY_QUESTION', 'survey_question');
define('TABLE_SURVEY_QUESTION_OPTION', 'survey_question_option');
define('TABLE_SURVEY_INVITATION', 'survey_invitation');
define('TABLE_SURVEY_ANSWER', 'survey_answer');


/*
==============================================================================
		DATABASE CLASS
		the class and its functions
==============================================================================
*/
/**
 *	@package dokeos.library
 */
class Database
{
	/*
	-----------------------------------------------------------------------------
		Accessor Functions
		Usually, you won't need these directly but instead
		rely on of the get_xxx_table functions.
	-----------------------------------------------------------------------------
	*/
	/**
	 *	Returns the name of the main Dokeos database.
	 */
	function get_main_database()
	{
		global $_configuration;
		return $_configuration['main_database'];
	}
	/**
	*	Returns the name of the Dokeos statistics database.
	*/
	function get_statistic_database()
	{
		global $_configuration;
		return $_configuration['statistics_database'];
	}
	/**
	*	Returns the name of the Dokeos SCORM database.
	*/
	function get_scorm_database()
	{
		global $_configuration;
		return $_configuration['scorm_database'];
	}
	/**
	*	Returns the name of the database where all the personal stuff of the user is stored
	*/
	function get_user_personal_database()
	{
		global $_configuration;
		return $_configuration['user_personal_database'];
	}
    /**
    * Returns database connection to ads DB
    */
    function get_ads_database() {
        global $_configuration;
        return new mysqli($_configuration['db_host'], $_configuration['db_user'], $_configuration['db_password'], $_configuration['ads_database']);
    }
    /**
    * Return DB connection to newsletter
    */
    function get_main_database_conn() {
        global $_configuration;
        return new mysqli($_configuration['db_host'], $_configuration['db_user'], $_configuration['db_password'], 'dokeos_main');
    }
    function get_stats_database_conn() {
        global $_configuration;
        return new mysqli($_configuration['db_host'], $_configuration['db_user'], $_configuration['db_password'], 'dokeos_stats');
    }
	/**
	*	Returns the name of the main Dokeos database.
	*/
	function get_current_course_database()
	{
		$course_info = api_get_course_info();
		return $course_info["dbName"];
	}
	/**
	*	Returns the glued name of the current course database.
	*/
	function get_current_course_glued_database()
	{
		$course_info = api_get_course_info();
		return $course_info["dbNameGlu"];
	}
	/**
	*	The glue is the string needed between database and table.
	*	The trick is: in multiple databases, this is a period (with backticks)
	*	In single database, this can be e.g. an underscore so we just fake
	*	there are multiple databases and the code can be written independent
	*	of the single / multiple database setting.
	*/
	function get_database_glue()
	{
		global $_configuration;
		return $_configuration['db_glue'];
	}
	/**
	*	Returns the database prefix.
	*	All created COURSE databases are prefixed with this string.
	*
	*	TIP: this can be convenient e.g. if you have multiple Dokeos installations
	*	on the same physical server.
	*/
	function get_database_name_prefix()
	{
		global $_configuration;
		return $_configuration['db_prefix'];
	}
	/**
	*	Returns the course table prefix for single database.
	*	Not certain exactly when this is used.
	*	Do research.
	*	It's used in local.inc.php.
	*/
	function get_course_table_prefix()
	{
		global $_configuration;
		return $_configuration['table_prefix'];
	}
	/*
	-----------------------------------------------------------------------------
		Table Name functions
		use these functions to get a table name for queries,
		instead of constructing them yourself.

		Backticks automatically surround the result,
		e.g. `COURSE_NAME`.`link`
		so the queries can look cleaner.

		Example:
		$table = Database::get_course_table(TABLE_DOCUMENT);
		$sql_query = "SELECT * FROM $table WHERE $condition";
		$sql_result = api_sql_query($sql_query,__FILE__,__LINE__);
		$result = mysql_fetch_array($sql_result);
	-----------------------------------------------------------------------------
	*/
	/**
	 * A more generic function than the other get_main_xxx_table functions,
	 * this one can return the correct complete name of any table of the main database of which you pass
	 * the short name as a parameter.
	 * Please define table names as constants in this library and use them
	 * instead of directly using magic words in your tool code.
	 *
	 * @param string $short_table_name, the name of the table
	 */
	function get_main_table($short_table_name)
	{
		$database = Database::get_main_database();
		return Database::format_table_name($database, $short_table_name);
	}
	/**
	 * A more generic function than the older get_course_xxx_table functions,
	 * this one can return the correct complete name of any course table of which you pass
	 * the short name as a parameter.
	 * Please define table names as constants in this library and use them
	 * instead of directly using magic words in your tool code.
	 *
	 * @param string $short_table_name, the name of the table
	 * @param string $database_name, optional, name of the course database
	 * - if you don't specify this, you work on the current course.
	 */
	function get_course_table($short_table_name, $database_name = '')
	{
		$database_name_with_glue = Database::fix_database_parameter($database_name);
		return Database::format_glued_course_table_name($database_name_with_glue, $short_table_name);
	}
	/**
	 * This generic function returns the correct and complete name of any statistic table
	 * of which you pass the short name as a parameter.
	 * Please define table names as constants in this library and use them
	 * instead of directly using magic words in your tool code.
	 *
	 * @param string $short_table_name, the name of the table
	 */
	function get_statistic_table($short_table_name)
	{
		$database = Database::get_statistic_database();
		return Database::format_table_name($database, $short_table_name);
	}
	/**
	 * This generic function returns the correct and complete name of any scorm
	 * table of which you pass the short name as a parameter. Please define
	 * table names as constants in this library and use them instead of directly
	 * using magic words in your tool code.
	 *
	 * @param string $short_table_name, the name of the table
	 */
	function get_scorm_table($short_table_name)
	{
		$database = Database::get_scorm_database();
		return Database::format_table_name($database, $short_table_name);
	}
	/**
	 * This generic function returns the correct and complete name of any scorm
	 * table of which you pass the short name as a parameter. Please define
	 * table names as constants in this library and use them instead of directly
	 * using magic words in your tool code.
	 *
	 * @param string $short_table_name, the name of the table
	 */
	function get_user_personal_table($short_table_name)
	{
		$database = Database::get_user_personal_database();
		return Database::format_table_name($database, $short_table_name);
	}
	/**
	*	Returns the isocode corresponding to the language directory given.
	*/
	function get_language_isocode($lang_folder)
	{
		$table = Database::get_main_table(TABLE_MAIN_LANGUAGE);
		$sql_query = "SELECT isocode FROM $table WHERE dokeos_folder = '$lang_folder'";
		$sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
		$result = mysql_fetch_array($sql_result);
		$result = $result['isocode'];
		return $result;
	}

	/*
	-----------------------------------------------------------------------------
		Query Functions
		these execute a query and return the result(s).
	-----------------------------------------------------------------------------
	*/

	/**
	*	@return a list (array) of all courses.
	* 	@todo shouldn't this be in the course.lib.php script?
	*/
	function get_course_list()
	{
		$table = Database::get_main_table(TABLE_MAIN_COURSE);
		$sql_query = "SELECT * FROM $table";
		$sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
		$result = api_store_result($sql_result);
		return $result;
	}

	/**
	*	Returns an array with all database fields for the specified course.
	*
	*	@param the real (system) code of the course (key of the main course table)
	* 	@todo shouldn't this be in the course.lib.php script?
	*/
	function get_course_info($course_code)
	{
		$table = Database::get_main_table(TABLE_MAIN_COURSE);
		$sql_query = "SELECT * FROM $table WHERE `code` = '$course_code'";
		$sql_result = api_sql_query($sql_query, __FILE__, __LINE__);
		$result = mysql_fetch_array($sql_result);
		$result = Database::generate_abstract_course_field_names($result);
		return $result;
	}
	/**
	*	@param $user_id (integer): the id of the user
	*	@return $user_info (array): user_id, lastname, firstname, username, email, ...
	*	@author Patrick Cool <patrick.cool@UGent.be>, expanded to get info for any user
	*	@author Roan Embrechts, first version + converted to Database API
	*	@version 30 September 2004
	*	@desc find all the information about a specified user. Without parameter this is the current user.
	* 	@todo shouldn't this be in the user.lib.php script?
	*/
	function get_user_info_from_id($user_id = '')
	{
		$table = Database::get_main_table(TABLE_MAIN_USER);
		if ($user_id == '')
		{
			return $GLOBALS["_user"];
		}
		else
		{
			$sql_query = "SELECT * FROM $table WHERE `user_id` = '$user_id'";
			$result = api_sql_query($sql_query, __FILE__, __LINE__);
			$result_array = mysql_fetch_array($result);
			$result_array = Database::generate_abstract_user_field_names($result_array);
			return $result_array;
		}
	}

    function add_ads($name, $address, $email, $contact, $budget, $url,
        $is_img, $user_id, $keywords=array()) {
        $conn = Database::get_ads_database();
        $stmt_ins_ads = $conn->prepare("INSERT INTO ads
                            (name, address, email, contact, budget, url,
                            is_img, user_id, click_count)
                            VALUE (?, ?, ?, ?, ?, ?, ?, ?, 0)");
        $stmt_ins_key = $conn->prepare("INSERT INTO ads_keywords
                            (keyword_str) VALUE (?)");
        $stmt_ins_akl = $conn->prepare("INSERT INTO ads_keywords_link
                            (keywords_id, ads_id) VALUE (?, ?)");
        $stmt_ins_ads->bind_param('ssssdsii', $name, $address, $email,
            $contact, floatval($budget), $url, intval($is_img), $user_id);
        $stmt_ins_ads->execute();
        $ads_id = $conn->insert_id;
        foreach ($keywords as $keyword) {
            $stmt_ins_key->bind_param('s', $keyword);
            $stmt_ins_key->execute();
            $key_id = $conn->insert_id;
            $stmt_ins_akl->bind_param('ii', $key_id, $ads_id);
            $stmt_ins_akl->execute();
        }
        $stmt_ins_ads->close();
        $stmt_ins_key->close();
        $stmt_ins_akl->close();
        $conn->close();
        return $ads_id;
    }

    function get_ads_count() {
        $conn = Database::get_ads_database();
        $stmt = $conn->prepare("SELECT COUNT(*) FROM ads");
        $stmt->execute();
        $stmt->bind_result($count);
        while ($stmt->fetch()) { }
        $stmt->close();
        $conn->close();
        return $count;
    }

    function get_ads_by_id($ads_id) {
        $conn = Database::get_ads_database();
        $stmt = $conn->prepare("SELECT ads_id, url, user_id
                FROM ads WHERE ads_id=?");
        $stmt->bind_param('i', intval($ads_id));
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;
    }

    function bind_values($conn, $values, $count) {
        $sql = sprintf('SELECT A.ads_id, A.url, A.user_id
                FROM ads A, ads_keywords K, ads_keywords_link L
                WHERE A.ads_id=L.ads_id
                AND K.keywords_id = L.keywords_id
                AND K.keyword_str IN (%s)
                ORDER BY A.click_count
                LIMIT %d',
                implode(', ', array_fill(0, count($values), '?')), $count);
        $stmt = $conn->prepare($sql);
        $types = implode('', array_fill(0, count($values), 's'));
        switch (count($values)) {
            case 1:
                $stmt->bind_param($types, $values[0]);
                break;
            case 2:
                $stmt->bind_param($types, $values[0], $values[1]);
                break;
            case 3:
                $stmt->bind_param($types, $values[0], $values[1], $values[2]);
                break;
            case 4:
                $stmt->bind_param($types, $values[0], $values[1], $values[2],
                        $values[3]);
                break;
            case 5:
                $stmt->bind_param($types, $values[0], $values[1], $values[2],
                        $values[3], $values[4]);
                break;
        }
        return $stmt;
    }

    function get_ads_by_keywords($keywords, $count) {
        $conn = Database::get_ads_database();
        $stmt = $conn->prepare('SELECT ads_id, url, user_id
                FROM ads ORDER BY click_count LIMIT ?');
        $stmt->bind_param('i', intval($count));
        if (count($keywords) > 0) {
            // Allow maximum 5 keywords to be supplied
            $stmt = Database::bind_values($conn, array_slice($keywords, 0, 5),
                    $count);
        }
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;
    }

    function get_ads_by_user_id($user_id) {
        $conn = Database::get_ads_database();
        $stmt = $conn->prepare('SELECT ads_id, url, click_count
        FROM ads
        WHERE user_id = ?');
        $stmt->bind_param('i', intval($user_id));
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;
    }

//    function update_delivery_time($news_letter) {
//        $date = date_create($news_letter['delivery_time']);
//        if (new DateTime() > $date) {
//            $type = $news_letter['delivery_type'];
//            if ($type == 'd') {
//                date_add($date, date_interval_create_from_date_string("1 day"));
//            } else if ($type == 'w') {
//                date_add($date, date_interval_create_from_date_string("1 week"));
//            } else if ($type == 'm') {
//                date_add($date, date_interval_create_from_date_string("1 month"));
//            }
//            $conn = Database::get_main_database_conn();
//            $stmt = $conn->prepare('UPDATE newsletter
//                    SET delivery_time=?
//                    WHERE id=?');
//            $stmt->bind_param('si', date_format($date, 'Y-m-d H:i:s'), $news_letter['id']);
//            $stmt->execute();
//            $stmt->close();
//            $conn->close();
//        }
//    }

    function get_all_newsletters() {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare('SELECT id, delivery_time, delivery_type
                FROM newsletter
                ORDER BY delivery_time');
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;
    }

    function get_newsletter_by_id($newsletter_id) {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare('SELECT id, title, date_created, content
                FROM newsletter
                WHERE id=?');
        $stmt->bind_param('i', intval($newsletter_id));
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result[0];
    }

    function add_plugin($name, $desc, $path) {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("INSERT INTO plugins
                            (name, description, path)
                            VALUE (?, ?, ?)");
        $stmt->bind_param('sss', $name, $desc, $path);
        $stmt->execute();
        $plugin_id = $conn->insert_id;
        $stmt->close();
        $conn->close();
        return $plugin_id;
    }

    function get_plugins() {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare('SELECT id, name, description, path
                FROM plugins');
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;
    }

    function get_user_plugins($user_id) {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare('SELECT id
                FROM user_plugins
                WHERE user_id=?');
        $stmt->bind_param('i', intval($user_id));
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        if (count($result) > 0) {
            $it = new RecursiveIteratorIterator(new RecursiveArrayIterator($result));
            $plugins = array();
            foreach ($it as $v) {
                $plugins[] = $v;
            }
            return $plugins;
        }
        return $result;
    }

    function install_plugin($user_id, $plugin_id) {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("INSERT INTO user_plugins
                            (id, user_id)
                            VALUE (?, ?)");
        $stmt->bind_param('ii', intval($plugin_id), intval($user_id));
        $stmt->execute();
        $stmt->close();
        $conn->close();
        return $plugin_id;
    }

    function uninstall_plugin($user_id, $plugin_id) {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("DELETE FROM user_plugins
                            WHERE id=?
                            AND user_id=?");
        $stmt->bind_param('ii', intval($plugin_id), intval($user_id));
        $stmt->execute();
        $stmt->close();
        $conn->close();
        return $plugin_id;
    }

    function get_csrf_token($user_id) {
        $conn = Database::get_stats_database_conn();
        $stmt = $conn->prepare("SELECT token FROM csrf_token
                WHERE user_id=?");
        $stmt->bind_param('i', intval($user_id));
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result[0]['token'];
    }

    function getFileList(){
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("SELECT file.id, file.path, file.uploadId, user.username, file.createDateTime 
from (select path, max(createDateTime) as createDateTime
      from file
      group by path) as d1, file, user
where d1.path = file.path
and user.user_id = file.uploadId
and d1.createDateTime = file.createDateTime");
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;
    }

    function updateFileList($userId){
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("UPDATE file_notification SET isRead=1 WHERE tagId=?");
        $stmt->bind_param('i', intval($userId));
        $stmt->execute();
        $stmt->close();
        $conn->close();
        return $userId;
    }

    function getFileCommentList($fileId,$userId){
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("SELECT count(id) as count from file_notification where fileId=? and tagId=?");
        $stmt->bind_param('ii', intval($fileId), intval($userId));
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;
    }

    function getCommentList($fileId){
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("SELECT id, fileId, uploadId, user.username, text, createDatetime from comment, user where user.user_id = comment.uploadId and fileId=$fileId order by createDatetime");
        $stmt->bind_param('i', intval($fileId));
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;
    }

    function uploadFile($path, $uploadId,$tagArray,$date) {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("INSERT INTO file (path, uploadId, tagId, createDateTime)
        VALUES (?,?,?,?)");
        $stmt->bind_param('siss', $path, intval($uploadId), $tagArray, $date);
        $stmt->execute();
        $stmt->close();
        $conn->close();
        return $path;
    }

    function insertFileNotification($fileId, $value) {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("INSERT INTO file_notification (fileId, tagId, isRead)
            VALUES
            (?,?,0)");
        $stmt->bind_param('ii', intval($fileId), intval($value));
        $stmt->execute();
        $stmt->close();
        $conn->close();
        return $fileId;
    }

    function getLastFileId(){
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("SELECT MAX(id) as id FROM file");
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;

    }

    function sendComment($fileId,$uploadId,$comment,$date) {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("INSERT INTO comment (fileId, uploadId, text, createDateTime)
    VALUES (?,?,?,?)");
        $stmt->bind_param('iiss', intval($fileId), intval($uploadId),$comment,$date);
        $stmt->execute();
        $stmt->close();
        $conn->close();
        return $fileId;

    }

    function insertFileUser($fileId, $uploadId) {
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("INSERT INTO file_user(fileid, userid, isowner, canread, canedit)
								VALUES
								(?, ?, 1,1,1)");
        $stmt->bind_param('ii', intval($fileId), intval($uploadId));
        $stmt->execute();
        $stmt->close();
        $conn->close();
        return $fileId;
    }

    function getUserList(){
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("SELECT * FROM user");
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;

    }

    function checkNotification($tagId){
        $conn = Database::get_main_database_conn();
        $stmt = $conn->prepare("SELECT count(id) as count from file_notification where tagId=? and isRead=0");
        $stmt->bind_param('i', intval($tagId));
        $stmt->execute();
        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) { 
            $params[] = &$row[$field->name]; 
        } 
        call_user_func_array(array($stmt, 'bind_result'), $params); 
        while ($stmt->fetch()) { 
            foreach($row as $key => $val) { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 
        $stmt->close();
        $conn->close();
        return $result;
    }
    /**
     *	This creates an abstraction layer between database field names
     *	and field names expected in code.
     *
     *	This helps when changing database names.
     *	It's also useful now to get rid of the 'franglais'.
     *
     *	@todo	add more array entries to abstract course info from field names
     *	@author	Roan Embrechts
     *
     * 	@todo what's the use of this function. I think this is better removed.
     * 		  There should be consistency in the variable names and the use throughout the scripts
     * 		  for the database name we should consistently use or db_name or database (db_name probably being the better one)
     */
    function generate_abstract_course_field_names($result_array)
    {
        $result_array["official_code"] = $result_array["visual_code"];
        $result_array["visual_code"] = $result_array["visual_code"];
        $result_array["real_code"] = $result_array["code"];
        $result_array["system_code"] = $result_array["code"];
        $result_array["title"] = $result_array['title'];
        $result_array["database"] = $result_array["db_name"];
        $result_array["faculty"] = $result_array["category_code"];
        //$result_array["directory"] = $result_array["directory"];
        /*
           still to do: (info taken from local.inc.php)

           $_course['id'          ]         = $cData['cours_id'         ]; //auto-assigned integer
           $_course['name'        ]         = $cData['title'            ];
           $_course['official_code']        = $cData['visual_code'        ]; // use in echo
           $_course['sysCode'     ]         = $cData['code'             ]; // use as key in db
           $_course['path'        ]         = $cData['directory'        ]; // use as key in path
           $_course['dbName'      ]         = $cData['db_name'           ]; // use as key in db list
           $_course['dbNameGlu'   ]         = $_configuration['table_prefix'] . $cData['dbName'] . $_configuration['db_glue']; // use in all queries
           $_course['titular'     ]         = $cData['tutor_name'       ];
           $_course['language'    ]         = $cData['course_language'   ];
           $_course['extLink'     ]['url' ] = $cData['department_url'    ];
           $_course['extLink'     ]['name'] = $cData['department_name'];
           $_course['categoryCode']         = $cData['faCode'           ];
           $_course['categoryName']         = $cData['faName'           ];

           $_course['visibility'  ]         = (bool) ($cData['visibility'] == 2 || $cData['visibility'] == 3);
           $_course['registrationAllowed']  = (bool) ($cData['visibility'] == 1 || $cData['visibility'] == 2);
         */
        return $result_array;
    }
    /**
     *	This creates an abstraction layer between database field names
     *	and field names expected in code.
     *
     *	This helps when changing database names.
     *	It's also useful now to get rid of the 'franglais'.
     *
     *	@todo add more array entries to abstract user info from field names
     *	@author Roan Embrechts
     *	@author Patrick Cool
     *
     * 	@todo what's the use of this function. I think this is better removed.
     * 		  There should be consistency in the variable names and the use throughout the scripts
     */
    function generate_abstract_user_field_names($result_array)
    {
        $result_array['firstName'] 		= $result_array['firstname'];
        $result_array['lastName'] 		= $result_array['lastname'];
        $result_array['mail'] 			= $result_array['email'];
#$result_array['picture_uri'] 	= $result_array['picture_uri'];
#$result_array ['user_id'  ] 	= $result_array['user_id'   ];
        return $result_array;
    }


    /*
       -----------------------------------------------------------------------------
       Private Functions
       You should not access these from outside the class
       No effort is made to keep the names / results the same.
       -----------------------------------------------------------------------------
     */
    /**
     *	Glues a course database.
     *	glue format from local.inc.php.
     */
    function glue_course_database_name($database_name)
    {
        $prefix = Database::get_course_table_prefix();
        $glue = Database::get_database_glue();
        $database_name_with_glue = $prefix.$database_name.$glue;
        return $database_name_with_glue;
    }
    /**
     *	@param string $database_name, can be empty to use current course db
     *
     *	@return the glued parameter if it is not empty,
     *	or the current course database (glued) if the parameter is empty.
     */
    function fix_database_parameter($database_name)
    {
        if ($database_name == '')
        {
            $course_info = api_get_course_info();
            $database_name_with_glue = $course_info["dbNameGlu"];
        }
        else
        {
            $database_name_with_glue = Database::glue_course_database_name($database_name);
        }
        return $database_name_with_glue;
    }
    /**
     *	Structures a course database and table name to ready them
     *	for querying. The course database parameter is considered glued:
     *	e.g. COURSE001`.`
     */
    function format_glued_course_table_name($database_name_with_glue, $table)
    {
        $course_info = api_get_course_info();
        return "`".$database_name_with_glue.$table."`";
    }
    /**
     *	Structures a database and table name to ready them
     *	for querying. The database parameter is considered not glued,
     *	just plain e.g. COURSE001
     */
    function format_table_name($database, $table)
    {
        return "`".$database."`.`".$table."`";
    }
    /**
     * Count the number of rows in a table
     * @param string $table The table of which the rows should be counted
     * @return int The number of rows in the given table.
     */
    function count_rows($table)
    {
        $sql = "SELECT COUNT(*) AS n FROM $table";
        $res = api_sql_query($sql, __FILE__, __LINE__);
        $obj = mysql_fetch_object($res);
        return $obj->n;
    }
    /**
     * Gets the ID of the last item inserted into the database
     *
     * @author Yannick Warnier <yannick.warnier@dokeos.com>
     * @return integer The last ID as returned by the DB function
     * @comment This should be updated to use ADODB at some point
     */
    function get_last_insert_id()
    {
        return mysql_insert_id();
    }
    /**
     * Escapes a string to insert into the database as text
     * @param	string	The string to escape
     * @return	string	The escaped string
     * @author	Yannick Warnier <yannick.warnier@dokeos.com>
     * @author  Patrick Cool <patrick.cool@UGent.be>, Ghent University
     */
    function escape_string($string)
    {
        if (get_magic_quotes_gpc())
        {
            $string = stripslashes($string);
        }
        return mysql_real_escape_string($string);
    }
    /**
     * Gets the array from a SQL result (as returned by api_sql_query) - help achieving database independence
     * @param     resource    The result from a call to sql_query (e.g. api_sql_query)
     * @param     string      Optional: "ASSOC","NUM" or "BOTH", as the constant used in mysql_fetch_array.
     * @return    array       Array of results as returned by php
     * @author    Yannick Warnier <yannick.warnier@dokeos.com>
     */
    function fetch_array($res, $option = 'BOTH')
    {
        if ($option != 'BOTH')
        {
            if ($option == 'ASSOC')
            {
                return mysql_fetch_array($res, MYSQL_ASSOC);
            }
            elseif ($option == 'NUM')
            {
                return mysql_fetch_array($res, MYSQL_NUM);
            }
        }
        else
        {
            return mysql_fetch_array($res);
        }
    }
    /**
     * Gets the next row of the result of the SQL query (as returned by api_sql_query) in an object form
     * @param	resource	The result from a call to sql_query (e.g. api_sql_query)
     * @param	string		Optional class name to instanciate
     * @param	array		Optional array of parameters
     * @return	object		Object of class StdClass or the required class, containing the query result row
     * @author	Yannick Warnier <yannick.warnier@dokeos.com>
     */
    function fetch_object($res,$class=null,$params=null)
    {
        if(!empty($class))
        {
            if(is_array($params))
            {
                return mysql_fetch_object($res,$class,$params);
            }
            return mysql_fetch_object($res,$class);
        }
        return mysql_fetch_object($res);
    }
    /**
     * Gets the number of rows from the last query result - help achieving database independence
     * @param   resource    The result
     * @return  integer     The number of rows contained in this result
     * @author  Yannick Warnier <yannick.warnier@dokeos.com>
     **/
    function num_rows($res)
    {
        return mysql_num_rows($res);
    }

    function get_course_chat_connected_table($database_name = '')
    {
        $database_name_with_glue = Database::fix_database_parameter($database_name);
        return Database::format_glued_course_table_name($database_name_with_glue, CHAT_CONNECTED_TABLE);
    }

}
//end class Database
?>
